revOpenDatabase

Type

function

Summary

Connects to a MySQL, SQLite, PostgreSQL, ODBC or Oracle database.

Syntax

revOpenDatabase("mysql", <hostAddress>, <databaseName>, [<userName>],[<passWord>],[<useSSL>], [<socketAddr>], [<rwTimeout>], [<autoReconnect>])

revOpenDatabase("odbc", <hostAddress>, <databaseName>, [<userName>],[<passWord>],[<cursorType>])

revOpenDatabase("sqlite",<filePath>,[<sqliteOptions>])

revOpenDatabase("postgresql", <hostAddress>, <databaseName>, [<userName>], [<passWord>] [, <sslOption> ...])

revOpenDatabase("oracle", <hostAddress>, <databaseName>, [<userName>],[<passWord>])

Description

Use the revOpenDatabase function to start working with a database.

note

To use a DSN to identify an ODBC database, use the DSN as the host, and leave the databaseName parameter empty.

Important

The revOpenDatabase function is part of the Database library. To ensure that the function works in a standalone application, you must include this custom library when you create your standalone. In the Inclusions section of the General screen of the Standalone Application Settings window, make sure the Database Support checkbox is checked and the database drivers you are using are selected in the list of database drivers.

Important

If you are using any of the MySQL or PostgreSQL SSL connection options in a standalone application, make sure to select 'SSL Encryption' from among the available 'script libraries' in the standalone application settings panel.

The version of SQLite has been updated to 3.34.0.

The SQLite RTREE and FTS5 modules are now available.

SQLite loadable extensions are now supported. To utilize loadable extensions, the 'extensions' option must be passed to the revOpenDatabase() call when creating the database connection.

Binary data can now be placed into SQLite databases verbatim (without the encoding that used to occur) - this means databases can be made to contain binary data which is compatible with other applications. To utilize this functionality, the 'binary' option must be passed to the revOpenDatabase() call when creating the database connection.

URI filenames are now supported. To utilize this functionality, the 'uri' option must be passed to the revOpenDatabase() call when creating the database connection.

URI filenames are useful because they allow appending parameter strings to the filname. For example, in order to enable shared cache mode, you must open the database with a filename of the form:

file:pathtodbfile?cache=shared

You can also open a database for read only access:

file:pathtodbfile?mode=ro

The SQLite revOpenDatabase() call no longer requires 5 arguments and only requires a minimum of 2.

Important

To create and/or connect to a SQLite database on Linux, you now need to update GCC to LiveCode 4.9. To do this on Ubuntu, use the following commands in a terminal:

    sudo add-apt-repository ppa:ubuntu-toolchain-r/test
    sudo apt-get-update
    sudo apt-get install gcc-.4.9 g++-4.9

The version of the PostgreSQL library has been updated to 9.4.5.

Parameters

Name	Type	Description
hostAddress	string	A string specifying the IP address or domain name of the system hosting the database followed optionally with a number specifying the port number you want to connect to, and is used only for MySQL and PostgreSQL databases. If no port is specified, MySQL database connections use port 3306 and PostgreSQL database connections use port 5432. For SQLite databases, the host should be the full path to the database file.
databaseName	string	A string specifying the the name of the database to connect to. For SQLite databases, the database name should be left empty.
userName	string	A string specifying your authorized user name for the database (Some databases do not require a user name).
passWord	string	A string specifying the authentication password for the userName (Some databases do not require a password).
useSSL	bool	(MySQL Only) A boolean specifying whether to use SSL to secure the socket connection when connecting to a MySQL database. If you use this feature in a standalone application, remember to select 'SSL Encryption' from among the available 'script libraries' in the standalone application settings panel.
socketAddr	string	(MySQL Only) A string specifying the socket or named pipe the driver should use to connect with. If empty, the default for the host will be used.
rwTimeout	integer	(MySQL Only) A number specifying the database read/write timeout in whole seconds. Defaults to 20 seconds if empty or not a positive integer. If 0, the connection never times out.
autoReconnect	bool	(MySQL Only) A boolean specifying if the driver should attempt to automatically reconnect to the database when the connection is lost. A reconnection attempt will be made the next time a command is executed.
cursorType	enum	(ODBC Only) A string specifying the type of cursor to use.
sqliteOptions	enum	A comma-delimited list containing one or more items listed below (Note order of the items in the options parameter is not important):
filePath	string	(SQLite Only) A string specifying the path to the SQLite database.
sslOption	string	(PostgreSQL Only) A string of the form "key=value" specifying the SSL options to use when connecting. The key is the name of the option you want to set, the value is the value you want the option to take. Any number of key value pairs can be specified, each in a new parameter. The set of recognized SSL option and their expected values are as follows: "sslmode": A string specifying the SSL connection mode to use. If no SSL mode is specified, the default will be "prefer" if the security library can be loaded, "disable" if not. If you use SSL connections in a standalone application, remember to select 'SSL Encryption' from among the available 'script libraries' in the standalone application settings panel. There are 6 options: "disable": Only try a non-SSL connection. "allow": First try a non-SSL connection; if that fails, try an SSL connection. - \"prefer\": First try an SSL connection; if that fails, try a non-SSL connection. - \"require\": Only try an SSL connection. If a root CA file is present, verify the certificate in the same way as if verify-ca was specified. - \"verify-ca\": Only try an SSL connection, and verify that the server certificate is issued by a trusted certificate authority (CA). - \"verify-full\": Only try an SSL connection, verify that the server certificate is issued by a trusted CA and that the server host name matches that in the certificate. - **\"sslcompression\"**: A string specifying the any SSL compression to use. If set to \"1\" (default), data sent over SSL connections will be compressed. If set to \"0\", compression will be disabled. This parameter is ignored if a connection without SSL is made. Compression uses CPU time, but can improve throughput if the network is the bottleneck. Disabling compression can improve response time and throughput if CPU performance is the limiting factor. - **\"sslcert\"**: A string specifying the file name of the client SSL certificate, replacing the default ~/.postgresql/postgresql.crt. This parameter is ignored if an SSL connection is not made. - **\"sslkey\"**: A string specifying the location for the secret key used for the client certificate. This parameter is ignored if an SSL connection is not made. - **sslrootcert\"**: A string specifying the name of a file containing SSL certificate authority (CA) certificate(s). If the file exists, the server\'s certificate will be verified \ to be signed by one of these authorities. The default is ~/.postgresql/root.crt. - **\"sslcrl\"**: A string specifying the file name of the SSL certificate revocation list (CRL). Certificates listed in this file, if it exists, will be rejected while attempting \ to authenticate the server\'s certificate. The default is ~/.postgresql/root.crl.

Examples

get revOpenDatabase("mysql", "www.example.com", "RatesDB", myUsr, myPass)

get revOpenDatabase("odbc", "BizFile", , "jenkins" ,the dbPassword of me, "emulated static")

-- Using a fileDSN on Windows
local tConID
put revopendatabase("odbc","filedsn=C:\Users\JohnSmith\FileDsnSqlServer.dsn;Uid=<YOUR_USERNAME>;pwd=<YOUR_PASSWORD>",,,) into tConID

put revOpenDatabase("sqlite", "mydb.sqlite" )
-- open with legacy binary mode and loadable extensions disabled
put revOpenDatabase("sqlite", "mydb.sqlite", "binary" )
-- open the connection in the 'new' binary mode
put revOpenDatabase("sqlite", "mydb.sqlite", "extensions" )
-- enable loadable extensions for this connection
put revOpenDatabase("sqlite", "mydb.sqlite", "binary,extensions" )
-- enable both 'new' binary mode and loadable extensions
put revOpenDatabase("sqlite", "file:/Users/johnsmith/Desktop/mydb.sqlite", "uri" )
-- enable support for uri filenames for this connection

get revOpenDatabase("mysql", "localhost", "dbName", myUsr, myPass, false, "/var/mysql.sock", 1, true)

get revOpenDatabase("postgresql", "192.168.1.100", "dbName", myUsr, myPass, "sslmode=require", "sslcompression=0")

Related

command: revSetDatabaseDriverPath, revExecuteSQL

function: revLicenseType, revDatabaseID, revDatabaseConnectResult, revDatabaseTableNames, revQueryIsAtStart

library: Database library

Compatibility and Support

Introduced

LiveCode 1.1

OS

mac

windows

linux

ios

android

Platforms

desktop

server

mobile